Keys

Key attributes of an entity type#

An important constraint on the entities of an entity type is the key or uniqueness constraint on attributes. An entity type usually has one or more attributes whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely. For the EMPLOYEE entity type, a typical key attribute is Ssn (Social Security number) as each person has a unique social security number.

In an ER diagram, each key attribute has its name underlined inside the oval, as illustrated in the figure below:

Specifying that an attribute is a key of an entity type means that the preceding uniqueness property must hold for every entity set of the entity type. Hence, it is a constraint that prohibits any two entities from having the same value for the key attribute at the same time. This unique attribute is also known as the primary key.

Other examples of primary keys in the COMPANY database include Dept_Id which can be used to identify each department in the company. Also, Project_Id acts as the key attribute for the PROJECT entity.

Composite keys#

Sometimes a single attribute is not enough to uniquely identify each entity within an entity set.

Let’s take the example of a CAR entity. The VIN (vehicle identification number) attribute is formed from two simple component attributes, State and Number, neither of which is a key on its own, as both can never be unique for every car. However, their combination can uniquely identify every car in the country.

In cases like this, several attributes together form a key, meaning that the combination of the attribute values must be distinct for each entity. If a set of attributes possesses this property, the proper way to represent this in the ER model that we describe here is to define a composite attribute and designate it as a key attribute of the entity type. This is called a composite key.

So in the example of the CAR entity VIN is a composite key.

In the next lesson, we will move onto the next component in an ER diagram: relationships.